package sql;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

import model.Category;
import model.Fee;
import model.User;
/**
 * Util Class for MySQL operation.</br>
 * <b>NOTE:</b></br>You must confirm the class T is same as the <tt>clz</tt></br>
 * The Helper can be iterable which means you can use foreach.
 * each one one in the helper is one row in T table.</br>
 * See more at {@link MySQLHelper#MySQLHelper(Class)}
 * @param T the class you want to operate
 * 
 * @author TedaLIEz
 *
 */
public class MySQLHelper<T> implements Iterable<T>{
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/oop_project";
	private static final String USERNAME = "root";
	private static final String PWD = "";
	private ConnectionSource connectionSource;
	private Dao<T, Integer> dao;
	Class<T> clz;
	@SuppressWarnings("unused")
	private MySQLHelper() {
		
	}
	
	
	/**
	 * 
	 * public constructor of class
	 * 
	 * @param clz the className of T.
	 */
	public MySQLHelper(Class<T> clz) {
		this.clz = clz;
		try {
			connectDB();
			dao = DaoManager.createDao(connectionSource, clz);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	private void connectDB() throws SQLException {
		connectionSource = new JdbcConnectionSource(URL, USERNAME, PWD);
	}
	
	/**
	 * 
	 * select * from table
	 * 
	 * @return List contains all the data.
	 * @throws SQLException
	 */
	public List<T> selectAll() throws SQLException {
		return dao.queryForAll();
	}
	
	
	/**
	 * 
	 * insert an object into table
	 * 
	 * @param data the object
	 * @throws SQLException
	 */
	public boolean save(T data) throws SQLException {
		TableUtils.createTableIfNotExists(connectionSource, clz);
		if(dao.create(data) == 1) {
			dao.refresh(data);
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 
	 * findDataById
	 * 
	 * @param id
	 * @return data find by id
	 * @throws SQLException
	 */
	public T find(int id) throws SQLException {
		return dao.queryForId(id);
	}
	
	
	/**
	 * 
	 * delete from table by id
	 * 
	 * @param id
	 * @return true if delete success
	 * @throws SQLException
	 */
	public boolean delete(T data) throws SQLException {
		if (dao.delete(data) == 1) {
			return true;
		} else {
			return false;
		}
		
	}
	
	
	/**
	 * 
	 * update data already existed in the table.
	 * 
	 * @param data
	 * @return true if update success
	 * @throws SQLException
	 */
	public boolean update(T data) throws SQLException {
		if (dao.update(data) >= 1) {
			dao.refresh(data);
			return true;
		} else {
			return false;
		}
	}

	
	/**
	 * 
	 * 
	 * 
	 * @param id
	 * @param columnName
	 * @param expression
	 * @return
	 * @throws SQLException
	 */
	public boolean update(int id, String columnName, String expression) throws SQLException {
		UpdateBuilder<T, Integer> updateBuilder = dao.updateBuilder();
		String escapeColumn = updateBuilder.escapeColumnName(columnName);
		String escapeValue = updateBuilder.escapeValue(expression);
		updateBuilder.updateColumnExpression(escapeColumn, escapeValue).where().eq("id", id);
		if (dao.update(updateBuilder.prepare()) >= 1) {
			return true;
		} else {
			return false;
		}
	}
	
	
	/**
	 * 
	 * Query by column in the <tt>T</tt> table.
	 * 
	 * @param key the column in table(supported foreign obj)
	 * @param value the query value
	 * @return the rst List
	 * @throws SQLException
	 */
	public List<T> query(String key, Object value) throws SQLException {
		QueryBuilder<T, Integer> statementBuilder = dao.queryBuilder();
		statementBuilder.where().eq(key, value);
		return dao.query(statementBuilder.prepare());
	}
	
	
	/**
	 * 
	 * get the T Dao
	 * 
	 * @return the T Dao
	 */
	public Dao<T, Integer> getDao() {
		return dao;
	}
	
	/**
	 * 
	 * iterator of the helper,each one is one row in the table referred to <tt>T</tt>.
	 */
	@Override
	public Iterator<T> iterator() {
		// TODO Auto-generated method stub
		return dao.closeableIterator();
	}
	
	
	
	/**
	 * 
	 * Unit Test for MySQLHelper class
	 * 
	 * @param args
	 */
	public static void main(String[] args) {	
		Category cate = new Category("cate");
		MySQLHelper<Category> cateDao = new MySQLHelper<Category>(Category.class);
		User user = new User("guojian", "111", "1505");
		MySQLHelper<User> userDao = new MySQLHelper<User>(User.class);
		UpdateBuilder<User, Integer> updateBuilder = userDao.getDao().updateBuilder();
		try {
			updateBuilder.updateColumnValue("name", "test").where();
			userDao.getDao().update(updateBuilder.prepare());
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
	}


	
}
